﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using eCitizen.DataContracts;

namespace eCitizen.DataAccess
{
    public class BatchJobLoggingService : dbService
    {
        #region "BatchJob Logging"
        
        public int StartBatchJobWatch(int batchServiceType,DateTime batchStartTime)
        {
            int jobInstanceId = 0;

            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_InsertBatchInstance";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchServiceType";
            dbParameter.Value = batchServiceType;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchStartTime";
            dbParameter.Value = batchStartTime;
            dbCommand.Parameters.Add(dbParameter);

            jobInstanceId = Int32.Parse(dbCommand.ExecuteScalar().ToString());

            dbConnection.Close();

            return jobInstanceId;
        }
        public void StopBatchJobWatch(int batchInstanceId,DateTime batchEndTime)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_UpdateBatchInstance";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchInstanceId";
            dbParameter.Value = batchInstanceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchEndTime";
            dbParameter.Value = batchEndTime;
            dbCommand.Parameters.Add(dbParameter);

            dbCommand.ExecuteNonQuery();

            dbConnection.Close();
        }
        #endregion

        #region "ServiceInstance Logging"

        public int StartServiceInstanceWatch(int batchInstanceId, string agency, string serviceId,DateTime serviceStartTime)
        {
            int serviceInstanceId = 0;

            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_InsertBatchInstanceServiceLog";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchInstanceId";
            dbParameter.Value = batchInstanceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@Agency";
            dbParameter.Value = agency;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@Service";
            dbParameter.Value = serviceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ServiceStartTime";
            dbParameter.Value = serviceStartTime;
            dbCommand.Parameters.Add(dbParameter);

            serviceInstanceId = Int32.Parse(dbCommand.ExecuteScalar().ToString());

            dbConnection.Close();

            return serviceInstanceId;
        }

        public void StopServiceInstanceWatch(int batchServiceInstanceId, DateTime serviceEndTime)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_UpdateBatchInstanceServiceLog";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchInstanceId";
            dbParameter.Value = batchServiceInstanceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchEndTime";
            dbParameter.Value = serviceEndTime;
            dbCommand.Parameters.Add(dbParameter);

            dbCommand.ExecuteNonQuery();

            dbConnection.Close();
        }
        #endregion

        #region "Request Logging"
        public List<RequestEntry> LogAlertServiceRequests(int batchServiceInstanceId, List<RequestEntry> entries)
        {
            List<RequestEntry> modifiedRequestList = new List<RequestEntry>();
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            foreach (RequestEntry requestEntry in entries)
            {
                RequestEntry modifiedRequest = LogServiceRequests(batchServiceInstanceId,requestEntry, dbConnection);
                modifiedRequestList.Add(modifiedRequest);
            }

            dbConnection.Close();

            return modifiedRequestList;
        }

        private RequestEntry LogServiceRequests(int batchServiceInstanceId,RequestEntry request, SqlConnection dbConnection)
        {

            RequestEntry requestEntry = request;

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_InserteAlertsRequestLog";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchServiceId";
            dbParameter.Value = batchServiceInstanceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@TargetNricOrAll";
            dbParameter.Value = requestEntry.TargetNricOrAll;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@MessageDescription";
            dbParameter.Value = requestEntry.MessageDescription;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@Url";
            dbParameter.Value = requestEntry.Url;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@DateDue";
            dbParameter.Value = requestEntry.DateDue;
            dbParameter.DbType = DbType.DateTime;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@DateExpired";
            dbParameter.Value = requestEntry.DateExpired;
            dbParameter.DbType = DbType.DateTime;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@AmountDue";
            dbParameter.Value = requestEntry.AmountDue;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@PaymentStatus";
            dbParameter.Value = requestEntry.PaymentStatus;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@Gender";
            dbParameter.Value = requestEntry.Gender;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BirthStartDate";
            dbParameter.Value = requestEntry.BirthStartDate;
            dbParameter.DbType = DbType.DateTime;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BirthEndDate";
            dbParameter.Value = requestEntry.BirthEndDate;
            dbParameter.DbType = DbType.DateTime;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@MaritalStatus";
            dbParameter.Value = requestEntry.MartialStatus;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ResidentialStatus";
            dbParameter.Value = requestEntry.ResidentialStatus;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@EmploymentStatus";
            dbParameter.Value = requestEntry.ResidentialStatus;
            dbCommand.Parameters.Add(dbParameter);

            requestEntry.RequestId = Int32.Parse(dbCommand.ExecuteScalar().ToString());

            return requestEntry;

        }
        #endregion

        #region "Subscriber Logging"

        public void LogSubscribersForBroadcast(int batchServiceInstanceId, List<Subscriber> entries)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            foreach (Subscriber subscriber in entries)
            {
                LogSubscribersForBroadcast(batchServiceInstanceId, subscriber, dbConnection);
            }
            dbConnection.Close();
        }

        private void LogSubscribersForBroadcast(int batchServiceInstanceId, Subscriber subscriberInfo, SqlConnection dbConnection)
        {
            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_InserteAlertsSubscriberLog";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchServiceId";
            dbParameter.Value = batchServiceInstanceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@RequestId";
            dbParameter.Value = subscriberInfo.RequestId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@SubscriberNRIC";
            dbParameter.Value = subscriberInfo.NRIC;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@EmailSubscription";
            dbParameter.Value = subscriberInfo.EmailSubscription;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@SmsSubscription";
            dbParameter.Value = subscriberInfo.SmsSubscription;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@EmailRecipient";
            dbParameter.Value = subscriberInfo.Email;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@SmsRecipient";
            dbParameter.Value = subscriberInfo.HandPhone;
            dbCommand.Parameters.Add(dbParameter);

            dbCommand.ExecuteNonQuery();

        }

        #endregion

        #region "Processing File Logging"

        public void LogeAlertFileToProcess(int batchServiceInstanceId,string fileName,bool isRequestFile,DateTime processingDate)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_InsertBatchFileLog";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchServiceId";
            dbParameter.Value = batchServiceInstanceId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@FileName";
            dbParameter.Value = fileName;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@IsRequestFile";
            dbParameter.Value = isRequestFile;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ProcessDateTime";
            dbParameter.Value = processingDate;
            dbCommand.Parameters.Add(dbParameter);

            dbCommand.ExecuteNonQuery();

            dbConnection.Close();
        }

        public int GetRunningFileNo(int batchServiceType,DateTime processingDate)
        {
            int currentRunningNo = 0;
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_GetRunningFileNo";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@BatchServiceType";
            dbParameter.Value = batchServiceType;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ProcessDateTime";
            dbParameter.Value = processingDate;
            dbCommand.Parameters.Add(dbParameter);

            currentRunningNo = Int32.Parse(dbCommand.ExecuteScalar().ToString());

            dbConnection.Close();

            return currentRunningNo;
        }
        #endregion
    }
}
